package cn.com.common.tools;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {
	/**
	 *  excel export
	 * @param response
	 * @param list input list
	 * @param excelname  file name
	 * @param titles excel fields name
	 * @return export state
	 */
	public static <T> String ExportExcel(HttpServletResponse response,
			List<T> list, String excelname, String... titles) {
		if (excelname == null || excelname.isEmpty()) {
			excelname = new Date().getTime() + "";
		}
		HSSFWorkbook hbook = Hssbook(list, excelname, titles);
		response.setContentType("application/vnd.ms-excel");
		try {
			excelname = new String(excelname.getBytes("UTF-8"), "ISO-8859-1");
		} catch (Exception e2) {
		}
		response.setHeader("Content-disposition", "attachment;filename="
				+ excelname + ".xls");
		OutputStream ouputStream = null;
		try {
			ouputStream = response.getOutputStream();
			hbook.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();
			hbook.close();
		} catch (Exception e) {
			try {
				ouputStream.close();
				hbook.close();
			} catch (Exception e1) {
			}
			return "false";
		}
		return "ok";
	}

	/**
	 * excel read
	 * @param filepath
	 * @param c read class
	 * @return new list
	 * @throws IOException
	 * @throws Exception
	 * @throws IllegalAccessException
	 */
	public static <T> List<T> ReadExcel(String filepath, Class<T> c)
			throws IOException, Exception, IllegalAccessException {
		List<T> list = new ArrayList<T>();
		Field[] fields = c.getDeclaredFields();
		InputStream is = new FileInputStream(filepath);
		@SuppressWarnings("resource")
		HSSFWorkbook hbook = new HSSFWorkbook(is);
		HSSFSheet xssfSheet = hbook.getSheetAt(0);
		if (xssfSheet == null) {
			return list;
		}
		for (int rowNum = 1, rowcount = xssfSheet.getLastRowNum(); rowNum <= rowcount; rowNum++) {
			HSSFRow xssfRow = xssfSheet.getRow(rowNum);
			if (xssfRow == null) {
				continue;
			}
			T obj = c.newInstance();
			for (int celNum = 0, celCount = xssfRow.getLastCellNum(); celNum < celCount; celNum++) {
				HSSFCell hssCel = xssfRow.getCell(celNum);
				String result = "";
				if (hssCel != null) {
					result = getValue(hssCel);
				}
				fields[celNum].setAccessible(true);
				fields[celNum].set(obj, result);
			}
			list.add(obj);
		}
		return list;
	}

	private static <T> HSSFWorkbook Hssbook(List<T> list, String excelname,
			String... titles) {

		HSSFWorkbook hbook = new HSSFWorkbook();
		HSSFSheet sheet = hbook.createSheet(excelname);
		HSSFRow row = sheet.createRow((int) 0);
		HSSFCellStyle hstyle = hbook.createCellStyle();
		hstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		int count = titles.length;
		for (int i = 0; i < count; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(titles[i]);
			cell.setCellStyle(hstyle);
			sheet.autoSizeColumn(i);
		}
		Field[] fields = list.get(0).getClass().getDeclaredFields();
		int fcoount = fields.length > count ? count : fields.length;
		for (int i = 0, countl = list.size(); i < countl; i++) {
			row = sheet.createRow(i + 1);
			for (int j = 0; j < fcoount; j++) {
				Field field = fields[j];
				field.setAccessible(true);
				String fileString = "";
				try {
					Object obj = field.get(list.get(i));
					if (obj != null) {
						fileString = obj.toString();
					}
					row.createCell(j).setCellValue(fileString);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return hbook;
	}

	@SuppressWarnings({ "static-access" })
	private static String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
}
